<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
           
// Set flag that this is a parent file.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';


// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();
         
// Return the response.
//echo $app;

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';


$objPHPExcel = new PHPExcel();

// Set properties
//echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Wmc")
                             ->setLastModifiedBy("Wmc")
                             ->setTitle("Transaction Report")
                             ->setSubject("Transaction Report")
                             ->setDescription("Transaction Report")
                             ->setKeywords("Transaction Report")
                             ->setCategory("Transaction Report");


// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:L1');
$activeSheet->setCellValue('A1', 'Member Spending Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);


/************** FILL HEADER **************/
$index = 5;
$activeSheet->setCellValue('A'.$index, 'Name');
$activeSheet->setCellValue('B'.$index, 'Member ID');
$activeSheet->setCellValue('C'.$index, 'Phone');
$activeSheet->setCellValue('D'.$index, 'Job');
$activeSheet->setCellValue('E'.$index, 'Company name');
$activeSheet->setCellValue('F'.$index, 'Outlet');

$activeSheet->setCellValue('G'.$index, 'Total Visit');
$activeSheet->setCellValue('H'.$index, 'Total Spend');
$activeSheet->setCellValue('I'.$index, 'Points');
$activeSheet->setCellValue('J'.$index, 'Food');
$activeSheet->setCellValue('K'.$index, 'BV');
$activeSheet->setCellValue('L'.$index, 'Misc');

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L');
foreach($cols as $item)
{
    if($item == 'A')
    {
        setBackgroundColor($activeSheet, $item, 5, 25);
    }
    else if($item == 'E' || $item == 'F')
    {
        setBackgroundColor($activeSheet, $item, 5, 32);
    }
    else
    {
        setBackgroundColor($activeSheet, $item, 5);   
    }
}
/************ PRE INIT ************/
$mainframe = &JFactory::getApplication();
$context = JRequest::getCmd('option');
$view = JRequest::getCmd('view');
$db = JFactory::getDbo();
/*$db->setQuery($query);
$data = $db->loadObjectList();*/


/************** Outlet list **************/
$activeSheet->mergeCells('A3:L3');
if(strval($_GET['o']) != '')
{    
    
    $query = ' 
        SELECT
          `o`.NAME as `name`
        FROM #__wrd_outlets `o`
        WHERE `o`.CODE IN ('.strval($_GET['o']).')
        ORDER BY `o`.NAME ASC 
    ';   
    $db->setQuery($query) ;
    $outlets = $db->loadObjectList();
    $str = '';
    if(!empty($outlets))
    {
        foreach($outlets as $item)
        {
            $str.= $item->name.', ';
        }
    }
    
    $activeSheet->setCellValue('A3', 'Outlet: '.trim($str, ', '));   
}
else
{
    $activeSheet->setCellValue('A3', 'Outlet: All');   
}
$activeSheet->getStyle('A3')->getFont()->setSize(16);
$activeSheet->getStyle('A3')->getFont()->setBold(true);

/************* Query Data *************/
$query = "
    SELECT
      `t`.id             AS `id`,
      `t`.vip_number     AS `vip_number`,
      SUM(`t`.total)     AS `total`,
      SUM(`t`.beverage)  AS `beverage`,
      SUM(`t`.food)      AS `food`,
      SUM(`t`.other)     AS `other`,
      COUNT(`t`.transaction_id) AS `total_visit`,
      `t`.transaction_id AS `check`,
      `t`.liquor         AS `liquor`,
      `o`.NAME           AS `outlet`,
      `p`.gender         AS `gender`,
      `p`.l_name         AS `l_name`,
      `p`.f_name         AS `f_name`,
      `p`.company_name   AS `company`,
      `p`.p_email        AS `email`,
      `p`.phone          AS `phone`,
      `j`.NAME           AS `job`
    FROM #__wrd_outlets `o`, #__wrd_transaction `t`
      LEFT JOIN #__wrd_profile `p`
        ON `p`.member_id = `t`.vip_number
      LEFT JOIN #__wrd_job `j`
        ON `j`.id = `p`.job
    WHERE `t`.CONVERT = 1                    
        AND `o`.cid = '01' 
        AND `t`.outlet = `o`.CODE
";


/************ CHECK CONDITIONS ************/
if(strval($_GET['o']) != '')
{    
    $query .= ' AND `o`.CODE IN ('.strval($_GET['o']).') ';
}

if(strval($_GET['f']) != ''){
    $query .= ' AND `t`.date_receipt >= "'.strval($_GET['f']).' 00:00:00'.'"'; 
}
else {
	$query .= ' AND `t`.date_receipt >= "'.date("Y-m")."-01".' 00:00:00'.'"';
}

if(strval($_GET['t']) != ''){
    $query .= ' AND `t`.date_receipt <= "'.strval($_GET['t']).' 23:59:59'.'" ' ; 
}
else {
	$query .= ' AND `t`.date_receipt <= "'.date("Y-m-d").' 23:59:59'.'" ' ;
}

$query .= '
    GROUP BY `t`.vip_number
    ORDER BY total DESC
';
if(intval($_GET['l']) > 0)
{
    $query .= ' LIMIT 0, '.intval($_GET['l']);    
}

echo $query; die;
            
$db->setQuery($query) ;
$data = $db->loadObjectList();

/******************* BIND DATA TO EXCEL *******************/
if(!empty($data))
{
    $transaction_id = '';
    $size = count($data);
    
/************* Get transaction id *************/
    $vip_number = '';
    $size = count($data);
    for($i=0; $i<$size; $i++)
    {
        $vip_number .= $data[$i]->vip_number.',';
    }
    $vip_number = trim($vip_number, ',');
    
    $query = 'SELECT
                `t`.transaction_id AS `transaction_id`,
                `t`.vip_number     AS `vip_number`,
                `t`.pax            AS `pax`
              FROM #__wrd_transaction `t`
              WHERE `t`.vip_number IN ('.$vip_number.')';
    $db->setQuery($query) ;
    $transactions = $db->loadObjectList();
    
    $transaction_id = '';
    $countTransaction = count($transactions);
    if(!empty($transactions))
    {
        for($i=0; $i<$countTransaction; $i++)
        {
            $transaction_id .= $transactions[$i]->transaction_id.',';
            $transactions[$i]->pax = 0;
        }
    }
    $transaction_id = trim($transaction_id, ',');
    
/************* Get point *************/
    $query = 'SELECT `point` as `point`, `transaction` as `transaction` 
    		FROM #__wrd_point WHERE `transaction` IN ('.$transaction_id.')';
    $db->setQuery($query) ;
    $point = $db->loadObjectList();
    $arrPoint = array();
    if(!empty($point))
    {
        // Each transaction has it's own points
        foreach($point as $item)
        {
            $arrPoint[$item->transaction] = $item->point;
        }
        
        $point = array();
        for($i=0; $i<$countTransaction; $i++)
        {
            $point[$transactions[$i]->vip_number] = intval($point[$transactions[$i]->vip_number]) + $arrPoint[$transactions[$i]->transaction_id];
        }                
    }
    
    if(!empty($point))
    {        
        /*********** BIND TO EXCEL ***********/
        $index = 6;
        for($i=0; $i<$size; $i++)
        {
            $activeSheet->setCellValue('A'.$index, $data[$i]->l_name.' '.$data[$i]->f_name);
            
            
            $activeSheet->setCellValue('B'.$index, 'V'.$data[$i]->vip_number);
            $activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            //$activeSheet->setCellValue('C'.$index, "`".$data[$i]->phone);       
            $activeSheet->setCellValueExplicit('C'.$index, $data[$i]->phone, PHPExcel_Cell_DataType::TYPE_STRING);     
            $activeSheet->getStyle('C'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $activeSheet->setCellValue('D'.$index, $data[$i]->job);
            $activeSheet->getStyle('D'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $activeSheet->setCellValue('E'.$index, $data[$i]->company);
            //$activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $activeSheet->setCellValue('F'.$index, $data[$i]->outlet);
                            
            $activeSheet->setCellValue('G'.$index, $data[$i]->total_visit);
            $activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $activeSheet->setCellValue('H'.$index, number_format($data[$i]->total, 0, ',', ','));
            $activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $activeSheet->setCellValue('J'.$index, number_format($data[$i]->food, 0, ',', ','));
            $activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            
            $activeSheet->setCellValue('K'.$index, number_format($data[$i]->beverage, 0, ',', ','));
            $activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            
            $activeSheet->setCellValue('L'.$index, number_format($data[$i]->other, 0, ',', ','));
            $activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            
            $activeSheet->setCellValue('I'.$index, $point[$data[$i]->vip_number]);
            $activeSheet->getStyle('I'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                
            $index++;
        }
        unset($pointList);
    }
    
}

$fileName = 'revenuereport';
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="wmc.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          

function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff');  
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
